package com.android.place360.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class DBAdapter {

	public static final String TAG = "DBAdapter";
	public static final String DATABASE_NAME = "dbsqlite_quickplaces";
	
	public static final String TABLE_PLACES = "tbl_places";
	public static final String P_ID = "p_id";
	public static final String P_NAME = "place_name";
	public static final String P_LAT = "lat";
	public static final String P_LNG = "lng";
	public static final String P_CATALOG = "catalog";
	public static final String P_PHONE = "phone";
	public static final String P_ADDRESS = "address";
	
	public static final String TABLE_CATALOG="tbl_catalog";
	public static final String C_ID = "c_id";
	public static final String C_NAME="name";
	public static final String C_SEARCH_KEY = "search_key";
	public static final String C_SUP_CATALOG = "sup_catalog";
	public static final String C_ICON = "icon";
	public static final int NO_SUP_CATALOG = 0;
	
	public static final int DATABASE_VERSION = 2;
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDB;
	
	private static final String CREATE_TABLE_PLACES = "create table if not exists "
				+TABLE_PLACES+" ("
				+P_ID+" integer primary key autoincrement, " 
				+P_NAME +" text, "
				+P_LAT +" double, "
				+P_LNG +" double, "
				+P_CATALOG +" integer, "
				+P_PHONE + " text, "
				+P_ADDRESS + " text );";				
	private static final String CREATE_TABLE_CATALOG = "create table if not exists "
				+TABLE_CATALOG+ " ("
				+C_ID + " integer primary key autoincrement, "
				+C_NAME +" text, "
				+C_SEARCH_KEY + " text, "
				+C_SUP_CATALOG + " integer, "
				+C_ICON +" integer);";
	
	private final Context mContext;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(CREATE_TABLE_PLACES);
			db.execSQL(CREATE_TABLE_CATALOG);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			
			db.execSQL("DROP TABLE IF EXISTS "+TABLE_PLACES);
			db.execSQL("DROP TABLE IF EXISTS "+TABLE_CATALOG);
			onCreate(db);
		}
		
	}

	public DBAdapter(Context ctx) {
		this.mContext = ctx;
	}

	public DBAdapter open() {
	
		mDbHelper = new DatabaseHelper(mContext, DATABASE_NAME, null,
				DATABASE_VERSION);
		mDB = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}
	

	public long addPlace(String name, String address, double lat, double lng, int catalog, String phone) {
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(P_NAME, name);
		inititalValues.put(P_ADDRESS, address);
		inititalValues.put(P_LAT, lat);
		
		inititalValues.put(P_LNG, lng);
		inititalValues.put(P_CATALOG, catalog);
		inititalValues.put(P_PHONE, phone);
		
		return mDB.insert(TABLE_PLACES, null, inititalValues);
	}
	public int updatePlace(int id, String name, String address, double lat, double lng, int catalog, String phone) {
		ContentValues inititalValues = new ContentValues();
		
		inititalValues.put(P_NAME, name);
		inititalValues.put(P_ADDRESS, address);
		inititalValues.put(P_LAT, lat);
		
		inititalValues.put(P_LNG, lng);
		inititalValues.put(P_CATALOG, catalog);
		inititalValues.put(P_PHONE, phone);
		
		return mDB.update(TABLE_PLACES, inititalValues, P_ID+ "=?", new String[]{String.valueOf(id)});
	}
	public long addCatalog(String name, String search_key, int sup_catalog, int icon) {
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(C_NAME, name);
		inititalValues.put(C_SEARCH_KEY, search_key);
		inititalValues.put(C_SUP_CATALOG, sup_catalog);
		inititalValues.put(C_ICON, icon);
		return mDB.insert(TABLE_CATALOG, null, inititalValues);
	}
	
	public int updateCatalog(int id, String name, String search_key, int sup_catalog, int icon) {
		ContentValues inititalValues = new ContentValues();
		
		inititalValues.put(C_NAME, name);
		inititalValues.put(C_SEARCH_KEY, search_key);
		inititalValues.put(C_SUP_CATALOG, sup_catalog);
		inititalValues.put(C_ICON, icon);
		return mDB.update(TABLE_CATALOG,  inititalValues, C_ID + "=?", new String[]{String.valueOf(id)});
	}
	
	public boolean deletePlace(long rowId) {
		return mDB.delete(TABLE_PLACES, P_ID + "=" + rowId, null) > 0;
	}
	
	public boolean deletePlaceByCatalog(long catalogId) {
		return mDB.delete(TABLE_PLACES, P_CATALOG + "=" + catalogId, null) > 0;
	}
	
	public boolean deleteCatalog(long rowId) {
		return mDB.delete(TABLE_CATALOG, C_ID + "=" + rowId, null) > 0;
	}
	
	public boolean deleteAllPlace() {
		return mDB.delete(TABLE_PLACES, null, null) > 0;
	}
	
	public boolean deleteAllCatalog() {
		return mDB.delete(TABLE_CATALOG, null, null) > 0;
	}
	
	
	public Cursor getAllPlaces() {
		return mDB.query(TABLE_PLACES, new String[] { P_ID, P_NAME, P_LAT, P_LNG, P_CATALOG,
				P_PHONE, P_ADDRESS }, null, null, null, null, null);
	}
	public Cursor getAllCatalog() {
		return mDB.query(TABLE_CATALOG, new String[] { C_ID, C_NAME, C_SEARCH_KEY, C_SUP_CATALOG, C_ICON}, null, null, null, null, null);
	}
	
	public Cursor getPlaceByID(int id){
		String selection = P_ID + "=" +id;
		return mDB.query(TABLE_PLACES, new String[] { P_ID, P_NAME, P_LAT, P_LNG, P_CATALOG,
				P_PHONE, P_ADDRESS }, selection, null, null, null, null);
	}
	
	public Cursor getPlacesByCatalog(int catalog){
		String selection = P_CATALOG + "=" +catalog;
		return mDB.query(TABLE_PLACES, new String[] { P_ID, P_NAME, P_LAT, P_LNG, P_CATALOG,
				P_PHONE, P_ADDRESS }, selection, null, null, null, null);
	}
	
	public Cursor getCatalogByID(int id){
		String selection = C_ID + "=" +id;
		return mDB.query(TABLE_CATALOG, new String[] { C_ID, C_NAME, C_SEARCH_KEY, C_SUP_CATALOG, C_ICON}, selection, null, null, null, null);
	}
}
